<?php
class Homepage_controller
{
	const DBTable = "tbl_homepage_controller";

	public $Homepage_id;	public $Version;	public $Is_live;	public $Status;	public $Is_deleted;	public $Createdate;	public $Updatedate;
	private $__mysql;

	public function __construct()
	{
		$this->Homepage_id = 0;		$this->Version = 0;		$this->Is_live = 0;		$this->Status = 1;		$this->Is_deleted = 0;		$this->Createdate = date("Y-m-d H:i:s");		$this->Updatedate = date("Y-m-d H:i:s");
		$this->__mysql = new MYSQL;
	}

	public function Save()
	{
		$sqlStr = "insert into ".self::DBTable."
				(version, is_live, status, createdate, updatedate)
				values
				(
				'".$this->Version."', 				'".$this->Is_live."', 				'".$this->Status."', 				'".$this->Createdate."', 				'".$this->Updatedate."'
				)";
			$this->__mysql->Exec_query($sqlStr);
			$this->Homepage_id = $this->__mysql->Get_insert_id();
			return $this->Homepage_id;
	}

	public function Update()
	{
		$sqlStr = "update ".self::DBTable." set
			version = '".$this->Version."', 			is_live = '".$this->Is_live."', 			status = '".$this->Status."', 			updatedate = '".$this->Updatedate."'
			where homepage_id = '".mysql_real_escape_string($this->Homepage_id)."'";
			$this->__mysql->Exec_query($sqlStr);
	}

	public function Quick_update($column = null, $value = null)
	{
		$sqlStr = "update ".self::DBTable." set
			".$column." = '".mysql_real_escape_string($value)."',
			 updatedate = '".$this->Updatedate."' 
			where homepage_id = '".mysql_real_escape_string($this->Homepage_id)."'";
			$this->__mysql->Exec_query($sqlStr);
	}

	public function Replace_homepage($homepage_id = 0)
	{
		//right, downgrade the current homepage to 0
		$sqlStr = "update ".self::DBTable." set
			is_live = 0, 
			updatedate = '".$this->Updatedate."'
			where is_live = 1";
			$this->__mysql->Exec_query($sqlStr);

		$sqlStr = "update ".self::DBTable." set
			is_live = 1, 
			updatedate = '".$this->Updatedate."'
			where homepage_id = ".$homepage_id;
			$this->__mysql->Exec_query($sqlStr);
	}
	
	public function List_all($limit = null, $offset = null)
	{
		$sqlStr = "select *
			from ".self::DBTable."
			where is_deleted = 0 
			order by title";
		if(isset($limit) && isset($offset)){
			$sqlStr .= " limit $offset, $limit";
		}
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		return $data_array = $this->__mysql->Get_data_array($recordset);
	}

	public function List_all_selectlist()
	{
		$sqlStr = "select homepage_id, title
			from ".self::DBTable."
			where is_deleted = 0 
			order by title";
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		return $data_array = $this->__mysql->Get_data_array($recordset);
	}

	public function Delete($homepage_id = 0)
	{
		$sqlStr = "update ".self::DBTable." set is_deleted = 1 where homepage_id = '".mysql_real_escape_string($homepage_id)."'";
			$this->__mysql->Exec_query($sqlStr);

		$sqlStr = "optimize table ".self::DBTable."";
			$this->__mysql->Exec_query($sqlStr);
	}

	public function Select($homepage_id = 0)
	{
		$sqlStr = "select * from ".self::DBTable." where homepage_id = '".mysql_real_escape_string($homepage_id)."'";
		$recordset = $this->__mysql->Exec_query($sqlStr);

		if($this->__mysql->Get_num_rows($recordset) > 0)
		{
			$result = $this->__mysql->Get_data_assoc($recordset);
				$this->Homepage_id = $result['homepage_id'];				$this->Version = $result['version'];				$this->Is_live = $result['is_live'];				$this->Status = $result['status'];				$this->Is_deleted = $result['is_deleted'];				$this->Createdate = $result['createdate'];				$this->Updatedate = $result['updatedate'];
			return true;
		}
		else
		{
			return false;
		}
	}

	public function Select_by($fieldname = null, $value = null)
	{
		if(!is_null($fieldname))
		{
			$sqlStr = "select * from ".self::DBTable." where ".$fieldname." = '".mysql_real_escape_string($value)."'";
			$recordset = $this->__mysql->Exec_query($sqlStr);

			if($this->__mysql->Get_num_rows($recordset) > 0)
			{
				$result = $this->__mysql->Get_data_assoc($recordset);
				$this->Homepage_id = $result['homepage_id'];				$this->Version = $result['version'];				$this->Is_live = $result['is_live'];				$this->Status = $result['status'];				$this->Is_deleted = $result['is_deleted'];				$this->Createdate = $result['createdate'];				$this->Updatedate = $result['updatedate'];
				return true;
			}
			else
			{
				return false;
			}
		}
	}


	public function Get_homepage_layout_content()
	{
		$temp_array = array();
	
		$sqlStr = "SELECT hco.homepage_object_id, hco.content_type, hco.object_content_id, hco.ordering, hco.social_date_period
		FROM tbl_homepage_controller_objects hco 
		inner join tbl_homepage_controller hc on hco.homepage_id = hc.homepage_id
		WHERE 
		hc.is_live = 1
		order by hco.ordering asc
		";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			$i = 0;
			foreach($data_array as $data)
			{
				$temp_array[$i] = $data;
			
				//based on the content_type, load in the content related to it
				switch($data['content_type'])
				{
					case  "workbig":
					case  "worksmall":
					case  "worksmallimg":
					case  "workbigsocial":
						//load in the work content
						$sqlStr = "select cpw.work_id, cpw.title, cpw.description, cpw.image, cpw.url_slug, 
						cp.title as campaign_title, cp.url_slug as campaign_url_slug, 
						c.title as client_title
						from 
						tbl_campaigns_work cpw 
						inner join tbl_campaigns cp on cpw.campaign_id = cp.campaign_id
						inner join tbl_clients c on cp.client_id = c.client_id
						where 
						cpw.work_id = ".$data['object_content_id']."";
						$recordset = $this->__mysql->Exec_query($sqlStr);
						$data_array = $this->__mysql->Get_data_array_assoc($recordset);
						$data_array_count = count($data_array);
						$j = 0;
						if($data_array_count > 0)
						{
							foreach($data_array as $data)
							{
								$temp_array[$i]['content'][$j] = $data;

								//load in the tweets if any
								$sqlStr = "select tweet_id, tweet, tweet_created, tweet_username, tweet_signoff from  tbl_campaigns_work_tweets
								where 
								work_id = ".$data['work_id']."";
								$recordset = $this->__mysql->Exec_query($sqlStr);
								$tweet_array = $this->__mysql->Get_data_array_assoc($recordset);

								$temp_array[$i]['content'][$j]['tweets'] = $tweet_array;
								$j++;
							}
						}
						
					break;
					case "newssmall":
					case "newsbig":
						//load in the work content
						$sqlStr = "select news_id, title, summary, date, image, url_slug from tbl_news where news_id = ".$data['object_content_id']."";
						$recordset = $this->__mysql->Exec_query($sqlStr);
						$data_array = $this->__mysql->Get_data_array_assoc($recordset);
						$temp_array[$i]['content'] = $data_array;
					break;
					case "social":
						$sqlStr = "select tweet_id, tweet_created, tweet, tweet_username, tweet_signoff
						from tbl_social_tweets 
						where 
						date(tweet_created) = '".$data['social_date_period']."'
						order by tweet_created asc";
						//echo $sqlStr."<br />";
						$recordset = $this->__mysql->Exec_query($sqlStr);
						$data_array = $this->__mysql->Get_data_array_assoc($recordset);
						$temp_array[$i]['content'] = $data_array;
					break;
				}
				$i++;
			}
		}
		return $temp_array;
	}

	public function Get_homepage_additional_content()
	{
		$temp_array = array();
	
		//ok get the work items that were not marked in the predefined homepage controller
		//work items
		$sqlStr = "select cpw.work_id, cpw.title, cpw.description, cpw.image, cpw.url_slug, cpw.date, 
			cp.title as campaign_title, cp.url_slug as campaign_url_slug, 
			c.title as client_title
			from 
			tbl_campaigns_work cpw 
			inner join tbl_campaigns cp on cpw.campaign_id = cp.campaign_id
			inner join tbl_clients c on cp.client_id = c.client_id
			where work_id not in (select hco.object_content_id from tbl_homepage_controller_objects hco 
			inner join tbl_homepage_controller hc on hc.homepage_id = hco.homepage_id where hc.is_live = 1 and work_id = hco.object_content_id and hco.content_type in ('worksmallimg', 'worksmall', 'workbigsocial', 'workbig'))
			and 
			cpw.show_homepage = 1 and 
			cpw.status = 1 and 
			cpw.is_deleted = 0 and 
			cp.status = 1
			order by cpw.createdate desc";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			$i = 0;
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "worksmall";
				$temp_array[$i]['date'] = $data['date']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep
				$temp_array[$i]['content'][0] = $data;
				$i++;
			}
		}

		//news items
		$sqlStr = "select news_id, title, summary, date, image, url_slug
			from 
			tbl_news n 
			where news_id not in (select hco.object_content_id from tbl_homepage_controller_objects hco 
			inner join tbl_homepage_controller hc on hc.homepage_id = hco.homepage_id where hc.is_live = 1 and news_id = hco.object_content_id and hco.content_type in ('newssmall', 'newsbig'))
			and 
			is_deleted = 0 and 
			status = 1
			order by n.date desc";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "newssmall";
				$temp_array[$i]['date'] = $data['date']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep
				$temp_array[$i]['content'][0] = $data;
				$i++;
			}
		}

		//social items
		$sqlStr = "select distinct(date(st.tweet_created)) as tweet_dates 
		from tbl_social_tweets st
		where
		date(st.tweet_created) not in (select hco.social_date_period from tbl_homepage_controller_objects hco where date(st.tweet_created) = hco.social_date_period)
		order by date(st.tweet_created) desc
		";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "social";
				$temp_array[$i]['date'] = $data['tweet_dates']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep

				//now load in the tweets by this date
				$sqlStr = "select tweet_id, tweet, date(tweet_created), tweet_username, tweet_signoff
				from tbl_social_tweets 
				where 
				date(tweet_created) = '".$data['tweet_dates']."' and 
				is_deleted = 0 and 
				status = 1
				";
				$recordset = $this->__mysql->Exec_query($sqlStr);
				$data_array = $this->__mysql->Get_data_array_assoc($recordset);
				$data_array_count = count($data_array_count);
				if($data_array_count > 0)
				{
					$temp_array[$i]['content'] = $data_array;
				}
				$i++;
			}
		}
		return $temp_array;
	}

	public function Get_work_page_layout_content()
	{
		$temp_array = array();
	
		$sqlStr = "SELECT hco.homepage_object_id, hco.content_type, hco.object_content_id, hco.ordering, hco.social_date_period
		FROM tbl_homepage_controller_objects hco 
		inner join tbl_homepage_controller hc on hco.homepage_id = hc.homepage_id
		WHERE 
		hc.is_live = 1 and
		content_type in ('workbig', 'worksmall', 'worksmallimg', 'workbigsocial')
		order by hco.ordering asc
		";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			$i = 0;
			foreach($data_array as $data)
			{
				$temp_array[$i] = $data;
			
				//load in the work content
				$sqlStr = "select cpw.work_id, cpw.title, cpw.description, cpw.image, cpw.url_slug, 
				cp.title as campaign_title, cp.url_slug as campaign_url_slug, 
				c.title as client_title
				from 
				tbl_campaigns_work cpw 
				inner join tbl_campaigns cp on cpw.campaign_id = cp.campaign_id
				inner join tbl_clients c on cp.client_id = c.client_id
				where 
				cpw.work_id = ".$data['object_content_id']."";
				$recordset = $this->__mysql->Exec_query($sqlStr);
				$data_array = $this->__mysql->Get_data_array_assoc($recordset);
				$temp_array[$i]['content'] = $data_array;
				$i++;
			}
		}

		//work items
		$sqlStr = "select cpw.work_id, cpw.title, cpw.description, cpw.image, cpw.url_slug, cpw.date, 
			cp.title as campaign_title, cp.url_slug as campaign_url_slug, 
			c.title as client_title
			from 
			tbl_campaigns_work cpw 
			inner join tbl_campaigns cp on cpw.campaign_id = cp.campaign_id
			inner join tbl_clients c on cp.client_id = c.client_id
			where work_id not in (select hco.object_content_id from tbl_homepage_controller_objects hco 
			inner join tbl_homepage_controller hc on hc.homepage_id = hco.homepage_id where hc.is_live = 1 and work_id = hco.object_content_id)
			and 
			cp.status = 1 and 
			cpw.status = 1 and 
			cpw.is_deleted = 0
			order by cpw.date desc";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "worksmall";
				$temp_array[$i]['date'] = $data['date']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep
				$temp_array[$i]['content'][0] = $data;
				$i++;
			}
		}
		
		return $temp_array;
	}

	public function Get_classics_page_layout_content()
	{
		$temp_array = array();

		//work items
		$sqlStr = "select cpw.work_id, cpw.title, cpw.description, cpw.image, cpw.url_slug, cpw.date, 
			cp.title as campaign_title, cp.url_slug as campaign_url_slug, 
			c.title as client_title
			from 
			tbl_campaigns_work cpw 
			inner join tbl_campaigns cp on cpw.campaign_id = cp.campaign_id
			inner join tbl_clients c on cp.client_id = c.client_id
			where 
			cpw.status = 1 and 
			cpw.is_deleted = 0 and 
			cpw.is_classic = 1
			order by cpw.createdate desc";
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			$i = 0;
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "worksmall";
				$temp_array[$i]['date'] = $data['date']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep
				$temp_array[$i]['content'][0] = $data;
				$i++;
			}
		}
		
		return $temp_array;
	}

	public function Get_latest_page_layout_content()
	{
		$temp_array = array();

		//work items
		$sqlStr = "select cpw.work_id, cpw.title, cpw.description, cpw.image, cpw.url_slug, cpw.date, 
			cp.title as campaign_title, cp.url_slug as campaign_url_slug, 
			c.title as client_title
			from 
			tbl_campaigns_work cpw 
			inner join tbl_campaigns cp on cpw.campaign_id = cp.campaign_id
			inner join tbl_clients c on cp.client_id = c.client_id
			where 
			cp.status = 1 and 
			cpw.status = 1 and 
			cpw.is_deleted = 0
			order by cpw.date desc
			"; //order by c.title asc, cp.title asc, cpw.title asc
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			$i = 0;
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "worksmall";
				$temp_array[$i]['date'] = $data['date']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep
				$temp_array[$i]['content'][0] = $data;
				$i++;
			}
		}
		
		return $temp_array;
	}

	public function Get_seach_content($search_term = null, $limit = null, $offset = null)
	{
		$temp_array = array();
	
		//ok get the work items that were not marked in the predefined homepage controller
		//work items
		$sqlStr = "select cpw.work_id, cpw.title, cpw.description, cpw.image, cpw.url_slug, cpw.date, 
			cp.title as campaign_title, cp.url_slug as campaign_url_slug, 
			c.title as client_title
			from 
			tbl_campaigns_work cpw 
			inner join tbl_campaigns cp on cpw.campaign_id = cp.campaign_id
			inner join tbl_clients c on cp.client_id = c.client_id
			where 
			(cpw.description like '%".$search_term."%' or cpw.description_brief like '%".$search_term."%' or cpw.description_solution like '%".$search_term."%' or c.title like '%".$search_term."%' or cp.title like '%".$search_term."%') and 
			cp.status = 1 and 
			cpw.status = 1 and 
			cpw.is_deleted = 0
			order by cpw.createdate desc";
		//echo $sqlStr . "<br />";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			$i = 0;
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "worksmall";
				$temp_array[$i]['date'] = $data['date']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep
				$temp_array[$i]['content'][0] = $data;
				$i++;
			}
		}

		//news items
		$sqlStr = "select news_id, title, summary, date, image, url_slug
			from 
			tbl_news n 
			where 
			(summary  like '%".$search_term."%' or  description like '%".$search_term."%')
			order by n.date desc";
		$recordset = $this->__mysql->Exec_query($sqlStr);
		$data_array = $this->__mysql->Get_data_array_assoc($recordset);
		$data_array_count = count($data_array);
		if($data_array_count > 0)
		{
			foreach($data_array as $data)
			{
				$temp_array[$i]['content_type'] = "newssmall";
				$temp_array[$i]['date'] = $data['date']; //ok this value is not actually used in the frontend BUT instead is used to sort this array by date from this level as its hard to order an array second level deep
				$temp_array[$i]['content'][0] = $data;
				$i++;
			}
		}

		return $temp_array;
	}
}
?>